Beyond “If then”-- Three Techniques for Cleaning Character Variables from Write-in Questions

In survey studies, cleaning answers to write-in questions can be difficult and time consuming, especially when the same response may be written in multiple ways. Misunderstanding of the survey question, unrecognizable handwriting, and negligence in data entry are major factors leading to data inaccuracies that are almost impossible to avoid. Writing a series of “if then” statements is a classic solution to cleaning data. However with growing datasets, the number of conditions to be tested grow too, until thousands of “if then” statements may be required. This paper presents three techniques that we used to clean up the country of birth questions in the Cancer Prevention Study-3 (CPS-3). Combining data merging and Excel spreadsheets, using LIKE and SOUND LIKE operators, and implementing join tables with compare functions in the SQL procedure not only reduced the workload and eases the stress in cleaning character variables but also added some flavors to this tedious task.